package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.OtherTargetApplication;
@Named
@Transactional
public class OtherTargetApplicationImpl implements OtherTargetApplication{

	
	private EntityRepository repository;

	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}

	/**
	 * excle中的数据插入到数据库
	 */
	@Override
	public void insertTarget(Map<String, Object> map,Integer listSize,String depCode) {
		// TODO Auto-generated method stub
		StringBuilder sql = new StringBuilder();
		Integer year = 0;
		Integer month = 0;
		Integer quarter = 0;
		StringBuilder DepStr =  new StringBuilder();
		DepStr.append(" select  DEP_CODE from DRGs.T_DEPARTMENT t where t.DEP_NAME = '"+depCode+"' ");
//		if("药剂科".equals(depCode)){
//			System.out.println();
//		}
		try {
			List<Object> obj = getRepository().createSqlQuery(DepStr.toString()).list();
			if(obj.size()>0)
			{
				depCode =obj.get(0).toString();
			}
			
			if(map.get("month") != null){
				if(map.get("month").toString().trim().length() > 0){
					month = new Integer(map.get("month").toString().trim());
					year = new Integer(map.get("year").toString().trim());
				}
				
			}
			if(map.get("quarter") != null){
				if(map.get("quarter").toString().trim().length() > 0){
					quarter =  new Integer(map.get("quarter").toString().trim());
					year = new Integer(map.get("year").toString().trim());
				}
				
			}
			/*try {
				year = new Integer(map.get("year").toString().trim());
				month = new Integer(map.get("month").toString().trim());
			} catch (NumberFormatException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}*/
			StringBuilder sqlStr = new StringBuilder();
			sqlStr.append(" select DEP_CODE from drgs.T_HOSP_OTHERTARGET t where t.DEP_CODE='"+depCode+"' and t.DATE_YEAR = "+year+" and t.DATE_MONTH = "+month+" ");
			List<Object> list = new ArrayList<Object>();
			StringBuilder sqlVed = new StringBuilder();
			sqlVed.append(" select DEP_CODE from drgs.T_HOSP_OTHERTARGET t where t.DEP_CODE='"+depCode+"' and t.DATE_YEAR = "+year+" and t.QUARTER = "+quarter+" ");
			if(month > 0){
				list = getRepository().createSqlQuery(sqlStr.toString()).list();
			}
			else if(quarter > 0){
				list = getRepository().createSqlQuery(sqlVed.toString()).list();
			}
			else{
				return ;
			}
			if(list.size()>0){
				sql = new StringBuilder();
				StringBuilder sqlF = new StringBuilder();
				sql.append(" update drgs.T_HOSP_OTHERTARGET  t set  ");
				for(int i=0;i<listSize;i++ ){
					
					if(map.get("F"+(i+1))==null ){
						continue;
					}
					else{
						if( map.get("F"+(i+1)).toString().trim().length() == 0){
							continue;
						}
						else{
							if(month>0){
								Double targetValuec = new Double(map.get("F"+(i+1)+"C").toString());
								Double targetValue = new Double(map.get("F"+(i+1)).toString());
								if(i==listSize-1){
									sqlF.append("F"+(i+1)+"C=" + targetValuec+",");
									sqlF.append("F"+(i+1)+"=" + targetValue);
								}
								else{
									sqlF.append("F"+(i+1)+"C=" + targetValuec+",");
									sqlF.append("F"+(i+1)+"=" + targetValue+",");
								}
							}else{
								Double targetValue = new Double(map.get("F"+(i+1)).toString());
								if(i==listSize-1){
									sqlF.append("F"+(i+1)+"=" + targetValue);
								}
								else{
									sqlF.append("F"+(i+1)+"=" + targetValue+",");
								}
							}
							
						}
						
					}
				}
				if(sqlF.length()>0){
					if(sqlF.charAt(sqlF.length()-1)==','){
						sqlF.deleteCharAt(sqlF.length()-1);
					}
					sql.append(sqlF);
					if(month > 0 ){
						sql.append(" where   t.DEP_CODE='"+depCode+"' and t.DATE_YEAR = "+year+" and t.DATE_MONTH = "+month+" ");
						if(sqlF.length()<=0) sql = new StringBuilder();
					}
					else if(quarter > 0){
						if(sqlF.length()<=0){
							sql.append(" t.REMARK  ='"+map.get("remark").toString().trim()+"'" );
						}else{
							sql.append(" , t.REMARK  ='"+map.get("remark").toString().trim()+"'" );
						}
						sql.append(" where   t.DEP_CODE='"+depCode+"' and t.DATE_YEAR = "+year+" and t.QUARTER = "+quarter+" ");
					}
					getRepository().createSqlQuery(sql.toString()).executeUpdate();
				}
			}
			else{
				sql = new StringBuilder();
				if(month > 0){
					sql.append(" insert into drgs.T_HOSP_OTHERTARGET(DEP_CODE,DATE_YEAR,DATE_MONTH, ");
				}
				if(quarter > 0){
					sql.append(" insert into drgs.T_HOSP_OTHERTARGET(DEP_CODE,DATE_YEAR,QUARTER,REMARK, ");
				}
				//String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
				for(int i=0;i<listSize;i++ ){
					if(month>0){
						if(i==listSize-1){
							sql.append("F"+(i+1)+"C,");
							sql.append("F"+(i+1));
						}
						else{
							sql.append("F"+(i+1)+"C,");
							sql.append("F"+(i+1)+",");
						}
					}else{
						if(i==listSize-1){
							sql.append("F"+(i+1));
						}
						else{
							sql.append("F"+(i+1)+",");
						}
					}
				}
				
				if(month > 0){
					sql.append(") values('"+depCode+"',"+year+","+month+",");
				}
				else if(quarter > 0){
					sql.append(") values('"+depCode+"',"+year+","+quarter+",'"+map.get("remark").toString().trim()+"',");
				}
				for(int i=0;i<listSize;i++ ){
					try {
						if(map.get("F"+(i+1))==null ){
							if(month>0){
								if(i==listSize-1){
									sql.append("null, null");
								}
								else{
									sql.append("null, null,");
								}	
							}else{
								if(i==listSize-1){
									sql.append("null");
								}
								else{
									sql.append("null,");
								}
							}
						}
						else{
							if(map.get("F"+(i+1)).toString().trim().length() == 0){
								if(month>0){
									if(i==listSize-1){
										sql.append("null, null");
									}
									else{
										sql.append("null, null,");
									}	
								}else{
									if(i==listSize-1){
										sql.append("null");
									}
									else{
										sql.append("null,");
									}
								}
							}
							else{
								if(month>0){
									Double targetValuec = new Double(map.get("F"+(i+1)+"C").toString());
									Double targetValue = new Double(map.get("F"+(i+1)).toString());
									if(i==listSize-1){
										sql.append(targetValuec+",");
										sql.append(targetValue);
									}
									else{
										sql.append(targetValuec+",");
										sql.append(targetValue+",");
									}
								}else{
									Double targetValue = new Double(map.get("F"+(i+1)).toString());
									if(i==listSize-1){
										sql.append(targetValue);
									}
									else{
										sql.append(targetValue+",");
									}
								}
							}
							
						}
					} catch (NumberFormatException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				sql.append(") \n");
				getRepository().createSqlQuery(sql.toString()).executeUpdate();
			}
		
		} catch (Exception e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		
		
	}

	/**
	 * 指标查询的具体数据
	 */
	@Override
	public List<Object[]> findTargetTable(Map map) {
		// TODO Auto-generated method stub
		Integer year = new Integer(map.get("year").toString());
		
		String month = map.get("month").toString();
		
		String depCode = map.get("depCode").toString();
		String targetType = map.get("targetType").toString();
		String targetTypeCodes = map.get("targetTypeCodes").toString();
		String [] depArr= depCode.split(","); 
		String [] monthArr= month.split(",");
		String[] targetTypeCodess = targetTypeCodes.split(",");
		StringBuilder sql = new StringBuilder();
		
		if(targetType.equals("drgs")){
			Integer benchyear = new Integer(map.get("benchyear").toString());
			String groupVersion = map.get("groupVersion").toString();
			sql.append("select  Fname,wa.depname ");
			for(int i=0,slength=monthArr.length;i<slength;i++){
				String thismonth="";
				if(monthArr[i].length()<2){
					thismonth = "0"+monthArr[i];
				}
				else{
					thismonth = monthArr[i];
				}
				sql.append(",sum(decode(wa.month, "+thismonth+", Fvalue,null)) month"+monthArr[i] +"\n");
			}
			sql.append(" from ( \n");
			sql.append(" SELECT depname , month, Fvalue, Fname from( \n ");
			sql.append(" select aa1.depname,aa1.depcode,aa1.month,aa1.weight,aa1.cmi,aa1.time,aa1.fee,aa1.ratio,aa1.rate,aa2.thorFoRatio,aa3.highLevleOper,aa4.avgdasRight \n");
			sql.append(" from ( \n");
			sql.append(" select  TO_CHAR( mr.OUT_DATE, 'MM') month,mr.OUT_DEP_CODE depcode,dt.DEP_NAME depname,SUM(A.AS_WEIGHT) weight,COUNT(DISTINCT p.PARDRG_CODE),ROUND(SUM(A.AS_WEIGHT)/COUNT(P.PARID),2) cmi , \n ");
			sql.append("  round(SUM(mr.IN_DAYS/A.AS_AVG_DAYS)/COUNT(P.PARID),2)  time, \n ");
			sql.append("  round(SUM(mr.COSTAMOUNT/A.AS_AVG_FEE)/COUNT(P.PARID),2) fee , \n ");
			sql.append("  ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.OUT_WAY='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ratio, \n ");
			sql.append("  ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='0' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='0'AND mr.OUT_WAY='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='0' then 1 else 0 end)),4)*100 rate \n");
			sql.append(" from (select b.pmainindex as id, b.p00290000 as out_date, b.P00300001 as out_dep_code, b.p00320000 as in_days, b.p00800000 as costamount, b.p00620001 as out_way from drgs.mr_ipinfo b) mr  \n");
			sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON mr.ID = P.PARID  \n");
			sql.append("  LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE and A.AS_YEAR ='"+benchyear+"' \n");
			sql.append("  left join DRGS.T_DEPARTMENT dt on mr.OUT_DEP_CODE = dt.DEP_CODE ");
			sql.append("  where A.AS_WEIGHT IS not NULL  and P.PARDRG_CODE is not null \n");
			sql.append(" and TO_CHAR( mr.OUT_DATE, 'YYYY') ='"+year+"' and p.VER_CODE = '"+groupVersion+"'and( ");
			for(int i=0;i<depArr.length;i++){
				if(i==0){
					sql.append(" mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
				else{
					sql.append(" or mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
				
			}
			sql.append(" )");
			sql.append("  group by TO_CHAR( mr.OUT_DATE, 'MM'),  mr.OUT_DEP_CODE,dt.DEP_NAME \n");
			sql.append("   ORDER BY cmi DESC  )aa1 \n");
			sql.append("   left join ( \n");
			sql.append("   select  TO_CHAR( mr.OUT_DATE, 'MM') month,mr.OUT_DEP_CODE depcode, \n");
			sql.append(" round(sum(case when (a.OPER_LEVEL = '3' or a.OPER_LEVEL = '4') then 1 else 0 end) / count(1),2) thorFoRatio\n");
			sql.append("   FROM  (select id, pmainindex as parid, PO1400 as is_oper, PO0301 as oper_level from drgs.mr_operation) A \n");
			sql.append(" LEFT JOIN (select b.pmainindex as id, b.p00290000 as out_date, b.P00300001 as out_dep_code, b.p00320000 as in_days, b.p00800000 as costamount, b.p00620001 as out_way from drgs.mr_ipinfo b) mr ON  mr.id= a.PARID \n");
			sql.append(" where is_oper ='1' ");
			sql.append(" and TO_CHAR( mr.OUT_DATE, 'YYYY') ='"+year+"' and(");
			for(int i=0;i<depArr.length;i++){
				if(i==0){
					sql.append(" mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
				else{
					sql.append(" or mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
			}
			sql.append(" )");
			sql.append("  group by TO_CHAR( mr.OUT_DATE, 'MM'),mr.OUT_DEP_CODE \n ) aa2 on aa2.month = aa1.month and aa1.depcode =aa2.depcode \n");
			sql.append("   left join (    select TO_CHAR( mr.OUT_DATE, 'MM') month,mr.OUT_DEP_CODE depcode , \n");
			sql.append(" round(sum(case when a.AS_RISK_LEVEL = '4' then 1 else 0 end)/count(top.id),4) highLevleOper \n");
			sql.append("     from (select id, pmainindex as parid, PO1400 as is_oper, PO0301 as oper_level from drgs.mr_operation) top \n");
			sql.append("    left join  (select b.pmainindex as id, b.p00290000 as out_date, b.P00300001 as out_dep_code, b.p00320000 as in_days, b.p00800000 as costamount, b.p00620001 as out_way from drgs.mr_ipinfo b) mr ON  mr.id= top.PARID \n");
			sql.append("     LEFT JOIN DRGS.T_PATIENTDRG P ON mr.ID = P.PARID  \n");
			sql.append("     LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE and A.AS_YEAR ='"+benchyear+"' \n");
			sql.append("      where P.PARDRG_CODE is not null  and top.is_oper ='0' \n");
			sql.append(" and TO_CHAR( mr.OUT_DATE, 'YYYY') ='"+year+"' and p.VER_CODE = '"+groupVersion+"' and(");
			for(int i=0;i<depArr.length;i++){
				if(i==0){
					sql.append(" mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
				else{
					sql.append(" or mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
			}
			sql.append(" )");
			sql.append("        group by TO_CHAR( mr.OUT_DATE, 'MM'),  mr.OUT_DEP_CODE \n");
			sql.append("    ) aa3 on aa3.month = aa1.month and aa3.depcode =aa1.depcode \n");
			sql.append("   left join ( \n");
			sql.append("    select round(sum(overright)/count(distinct(PARDRG_CODE)),4) avgdasRight ,month, depcode from( \n");
			sql.append("  select case when (round(sum(mr.IN_DAYS)/count(mr.id),4)-a.AS_AVG_DAYS) < 0 then 1 else 0 end overright,  \n");
			sql.append(" p.PARDRG_CODE,COUNT (1) OVER (PARTITION BY 1), \n");
			sql.append(" TO_CHAR( mr.OUT_DATE, 'MM') month,mr.OUT_DEP_CODE depcode \n");
			sql.append(" from (select b.pmainindex as id, b.p00290000 as out_date, b.P00300001 as out_dep_code, b.p00320000 as in_days, b.p00800000 as costamount, b.p00620001 as out_way from drgs.mr_ipinfo b) mr  \n");
			sql.append("  LEFT JOIN DRGS.T_PATIENTDRG P ON mr.ID = P.PARID \n");
			sql.append("  LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE and A.AS_YEAR ='"+benchyear+"' \n");
			sql.append("  where A.AS_WEIGHT IS not NULL  and P.PARDRG_CODE is not null  \n");
			sql.append(" and TO_CHAR( mr.OUT_DATE, 'YYYY') ='"+year+"' and p.VER_CODE = '"+groupVersion+"' and(");
			for(int i=0;i<depArr.length;i++){
				if(i==0){
					sql.append(" mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
				else{
					sql.append(" or mr.OUT_DEP_CODE='"+depArr[i]+"'");
				}
			}
			sql.append(" )");
			sql.append(" group by p.PARDRG_CODE, a.AS_AVG_DAYS, TO_CHAR( mr.OUT_DATE, 'MM'), mr.OUT_DEP_CODE) \n");
			sql.append(" group by month, depcode  \n");
			sql.append("   ) aa4 on aa4.month = aa1.month and aa4.depcode =aa1.depcode \n");
			sql.append("   ) unpivot (Fvalue for Fname in("+targetTypeCodes+") )  order by Fname \n");
			sql.append("   ) wa group by Fname, wa.depname order by Fname \n");
		
		}
		else{
			sql.append("select  Fname,tdp.DEP_NAME ");
			for(int i=0,slength=monthArr.length;i<slength;i++){
					sql.append(",sum(decode(DATE_MONTH, "+monthArr[i]+", Fvalue,null)) month"+monthArr[i] +", sum(decode(DATE_MONTH, "+monthArr[i]+", FCvalue,null)) month"+monthArr[i]+"c \n");
			}
			sql.append(" from ( \n");
//			if(targetType.equals("finance") || targetType.equals("common")){
			String upviots = "unpivot ((Fvalue, Fcvalue) for Fname in(";
			for(int i=0; i<targetTypeCodess.length; i++){
				String code = targetTypeCodess[i];
				if(i==targetTypeCodess.length-1){
					upviots += "("+code+", "+code+"C) as '"+code+"' ";
				}else{
					upviots += "("+code+", "+code+"C) as '"+code+"', ";
				}
			}
			upviots += "))";
				sql.append(" SELECT DEP_CODE , DATE_MONTH, Fvalue, fcvalue, Fname from drgs.T_HOSP_OTHERTARGET  "+upviots+" \n");
				sql.append(" where DATE_YEAR="+year+"\n");
				sql.append("and(");
				for(int i=0;i<monthArr.length;i++){
					if(i==0){
						sql.append(" DATE_MONTH="+monthArr[i]);
					}
					else{
						sql.append(" or DATE_MONTH="+monthArr[i]);
					}
					
				}
				sql.append(" )\n");
				sql.append("and(");
				for(int i=0;i<depArr.length;i++){
					if(i==0){
						sql.append(" DEP_CODE='"+depArr[i]+"'");
					}
					else{
						sql.append(" or DEP_CODE='"+depArr[i]+"'");
					}
					
				}
				sql.append(" ) ) tav left join drgs.T_DEPARTMENT tdp on tav.DEP_CODE = tdp.DEP_CODE group by  Fname,tdp.DEP_NAME  order by Fname  \n");
//			}
		}
		
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
/**
 * 
 *  @Description：查询全院科室名称和code
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.OtherTargetApplication#depList()
 */
	@Override
	public List<Object[]> depList(){
		StringBuilder sql = new StringBuilder();
		sql.append(" select dep.dep_name,dep.dep_code from drgs.t_department dep order by dep.dep_code  \n");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	
	/**
	 * @methodname :findQuarterTargetTable
	 * @Description : 医院指标数据查询
	 * @author:yujiaolong
	 * @return InvokeResult
	 * @date :2017年7月6日
	 */
	@Override
	public List<Object[]> findQuarterTargetTable(Map map) {
		// TODO Auto-generated method stub
		
		Integer year = new Integer(map.get("year").toString());
		
		String quarter = map.get("quarter").toString();
		
		String depCode = map.get("depCode").toString();
		String targetType = map.get("targetType").toString();
		String targetTypeCodes = map.get("targetTypeCodes").toString();
		String [] depArr= depCode.split(","); 
		String [] quarterArr= quarter.split(",");
		//String [] targetCodeArr = targetTypeCodes.split(",");
		StringBuilder sql = new StringBuilder();

		sql.append(" select DATE_YEAR,QUARTER, tdp.DEP_NAME,");
		
		sql.append("  REMARK, "+targetTypeCodes);
		sql.append(" from drgs.T_HOSP_OTHERTARGET tht  \n");
		sql.append(" left join drgs.T_DEPARTMENT tdp on tht.DEP_CODE = tdp.DEP_CODE  \n");
		sql.append(" where DATE_YEAR="+year);
		if(quarterArr.length > 0){
			sql.append(" and( ");
		}
		for(int i=0;i<quarterArr.length;i++){
			if(i==0){
				sql.append(" QUARTER="+quarterArr[i]);
			}
			else{
				sql.append(" or QUARTER="+quarterArr[i]);
			}
		}
		if(quarterArr.length > 0){
			sql.append(" )  \n");
		}
		if(depArr.length > 0){
			sql.append("and( ");
		}
		for(int i=0;i<depArr.length;i++){
			if(i==0){
				sql.append(" tht.DEP_CODE='"+depArr[i]+"'");
			}
			else{
				sql.append(" or tht.DEP_CODE='"+depArr[i]+"'");
			}
			
		}
		if(depArr.length > 0){
			sql.append(")  ");
		}
		
		
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

}
